package com.android;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.DebugUtils;
import android.util.Log;

public class MyDatabase {

    SQLiteDatabase mDb;
    DbHelper mDbHelper;
    Context mContext;
    private static final String DB_NAME="CountTimer";//nome del db
    private static final int DB_VERSION=3; //numero di versione del nostro db
    
    /*
    public static MyDatabase getInstance(Context ctx){
      if(INSTANCE == null ){
        this.INSTANCE = new MyDatabase(ctx) ;
      }

      return INSTANCE ;
    }
     *
     */

    public MyDatabase(Context ctx){
            mContext=ctx;
            mDbHelper=new DbHelper(ctx, DB_NAME, null, DB_VERSION);   //quando istanziamo questa classe, istanziamo anche l'helper (vedi sotto)
    }

    public void open(){  //il database su cui agiamo è leggibile/scrivibile
            mDb=mDbHelper.getWritableDatabase();

    }

    public void close(){ //chiudiamo il database su cui agiamo
            mDb.close();
    }


    //i seguenti 2 metodi servono per la lettura/scrittura del db. aggiungete e modificate a discrezione
   // consiglio:si potrebbe creare una classe Prodotto, i quali oggetti verrebbero passati come parametri dei seguenti metodi, rispettivamente ritornati. Lacio a voi il divertimento


    public void insertSession(String name){ //metodo per inserire i dati
            ContentValues cv=new ContentValues();
            cv.put(ProfileMetaData.SESSION_NAME_KEY, name);
            //cv.put(ProfileMetaData.PRODUCT_TEAM_KEY, team);
            mDb.insert(ProfileMetaData.SESSIONS_TABLE, null, cv);
    }

    public void insertRepeat(int id_session , String name , int seconds , int color){ //metodo per inserire i dati
            ContentValues cv=new ContentValues();
            cv.put(ProfileMetaData.REPEAT_SESSION_ID, id_session);
            cv.put(ProfileMetaData.REPEAT_NAME_KEY, name);
            cv.put(ProfileMetaData.REPEAT_SECOND_KEY, seconds);
            cv.put(ProfileMetaData.REPEAT_COLOR_KEY, color);
            //cv.put(ProfileMetaData.PRODUCT_TEAM_KEY, team);
            mDb.insert(ProfileMetaData.REPEAT_TABLE, null, cv);
    }

    public void deleteRepeat(int id_repeat){
      mDb.delete(ProfileMetaData.REPEAT_TABLE , ProfileMetaData.REPEAT_ID+"= ? " , new String[]{""+id_repeat});
    }

    public void deleteSession(int id_session){
      mDb.delete(ProfileMetaData.REPEAT_TABLE , ProfileMetaData.REPEAT_SESSION_ID+"= ? " , new String[]{""+id_session});
      mDb.delete(ProfileMetaData.SESSIONS_TABLE , ProfileMetaData.SESSION_ID+"= ? " , new String[]{""+id_session});
    }

    public void updateRepeatSession(int id_session , int repeat){
      ContentValues cv=new ContentValues();
      //cv.put(ProfileMetaData.REPEAT_SESSION_ID, id_session);
      cv.put(ProfileMetaData.SESSION_REPEAT_KEY, repeat);
      mDb.update(ProfileMetaData.SESSIONS_TABLE , cv, ProfileMetaData.SESSION_ID+"= ? " , new String[]{""+id_session});
    }

    public Cursor fetchSession(){ //metodo per fare la query di tutti i dati
      return mDb.query(ProfileMetaData.SESSIONS_TABLE, null,null,null,null,null,null);
    }

    public int fetchTimeSession(int id_session){
      Cursor c = mDb.query(ProfileMetaData.REPEAT_TABLE,new String[]{"SUM(second) as session_second"}, ProfileMetaData.REPEAT_SESSION_ID+"="+id_session,null,null,null,null);
      c.moveToFirst();
      return c.getInt(0) ;
    }

    public Cursor fetchRepeatFromPosition(int id_session , int position){
      return mDb.query(ProfileMetaData.REPEAT_TABLE,null, ProfileMetaData.REPEAT_SESSION_ID+"="+id_session,null,null,null,null , position+",1");
    }

    public Cursor fetchSession(int id_session){ //metodo per fare la query di tutti i dati
      //return mDb.query(ProfileMetaData.SESSIONS_TABLE, null,null,null,null,null,null);
      return mDb.query(ProfileMetaData.SESSIONS_TABLE, null,ProfileMetaData.SESSION_ID+"="+id_session,null,null,null,null);
    }

    public Cursor fetchRepeat(int id_session) {
      //COL_ROW_ID + "=" + id
      Log.i("INFO" , "Stringa per "+Integer.toString(id_session));
      return mDb.query(ProfileMetaData.REPEAT_TABLE, null,ProfileMetaData.REPEAT_SESSION_ID+"="+id_session,null,null,null,null);
    }

    public Cursor fetchRepeat(int id_session , int id_repeat) {
      //return mDb.rawQuery("SELECT * FROM "+ProfileMetaData.REPEAT_TABLE+" WHERE "+ProfileMetaData.REPEAT_SESSION_ID+" = '"+id_session+"' AND "+ProfileMetaData.REPEAT_ID+" = '"+id_repeat+"' ",null) ;
      //return mDb.rawQuery("SELECT * FROM "+ProfileMetaData.REPEAT_TABLE+" WHERE "+ProfileMetaData.REPEAT_SESSION_ID+" = '"+id_session+"' AND "+ProfileMetaData.REPEAT_ID+" = '"+id_repeat+"' ",new String[]{""+id_session , ""+id_repeat}) ;
      //return mDb.query(ProfileMetaData.REPEAT_TABLE, null,ProfileMetaData.REPEAT_SESSION_ID+" = ? AND "+ProfileMetaData.REPEAT_ID+" = ? ",new String[]{""+id_session,""+id_repeat},null,null,null);
      return mDb.query(ProfileMetaData.REPEAT_TABLE, null,ProfileMetaData.REPEAT_SESSION_ID+" = ? AND "+ProfileMetaData.REPEAT_ID+" = ? ",new String[]{""+id_session , ""+id_repeat},null,null,null);
    }

  public void editRepeat(int id_repeat, String name, int seconds, int color) {
    ContentValues cv=new ContentValues();
    //cv.put(ProfileMetaData.REPEAT_SESSION_ID, id_session);
    cv.put(ProfileMetaData.REPEAT_NAME_KEY, name);
    cv.put(ProfileMetaData.REPEAT_SECOND_KEY, seconds);
    cv.put(ProfileMetaData.REPEAT_COLOR_KEY, color);
    //cv.put(ProfileMetaData.PRODUCT_TEAM_KEY, team);
    mDb.update(ProfileMetaData.REPEAT_TABLE , cv, ProfileMetaData.REPEAT_ID+"= ? " , new String[]{""+id_repeat});
  }

    public static class ProfileMetaData {  // i metadati della tabella, accessibili ovunque
            static final String SESSIONS_TABLE = "session";
            static final String SESSION_ID = "_id";
            public static final String SESSION_NAME_KEY = "name";
            public static final String SESSION_REPEAT_KEY = "repeat_for";

            static final String REPEAT_TABLE = "repeat";
            static final String REPEAT_ID = "_id";
            static final String REPEAT_SESSION_ID = "_session_id";
            public static final String REPEAT_NAME_KEY = "name";
            public static final String REPEAT_SECOND_KEY = "second";
            public static final String REPEAT_COLOR_KEY = "color";

            //static final String PRODUCT_TEAM_KEY = "team";
    }

    private static final String SESSIONS_TABLE_CREATE = "CREATE TABLE IF NOT EXISTS "  //codice sql di creazione della tabella
                    + ProfileMetaData.SESSIONS_TABLE + " ("
                    + ProfileMetaData.SESSION_ID+ " integer primary key autoincrement, "
                    + ProfileMetaData.SESSION_NAME_KEY + " text not null);";

    private static final String REPEAT_TABLE_CREATE = "CREATE TABLE IF NOT EXISTS "  //codice sql di creazione della tabella
                    + ProfileMetaData.REPEAT_TABLE + " ("
                    + ProfileMetaData.REPEAT_ID+ " integer primary key autoincrement, "
                    + ProfileMetaData.REPEAT_SESSION_ID+ " integer not null , "
                    + ProfileMetaData.REPEAT_NAME_KEY + " text not null ,"
                    + ProfileMetaData.REPEAT_SECOND_KEY + " int not null ,"
                    + ProfileMetaData.REPEAT_COLOR_KEY + " varchar(6) not null"
                    + ");";

    private class DbHelper extends SQLiteOpenHelper { //classe che ci aiuta nella creazione del db

      public DbHelper(Context context, String name, CursorFactory factory,int version) {
        super(context, name, factory, version);
      }

      @Override
      public void onCreate(SQLiteDatabase _db) { //solo quando il db viene creato, creiamo la tabella
        _db.execSQL(SESSIONS_TABLE_CREATE);
        onUpgrade(_db, 1, DB_VERSION);
      }

      @Override
      public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
        //qui mettiamo eventuali modifiche al db, se nella nostra nuova versione della app, il db cambia numero di versione
        for(int appVersion = oldVersion+1 ; appVersion <= newVersion ; appVersion++ ){
          switch(appVersion){
            case 2:
              _db.execSQL(REPEAT_TABLE_CREATE);
            break ;
            case 3:
              _db.execSQL("ALTER TABLE "+ProfileMetaData.SESSIONS_TABLE+" ADD "+ProfileMetaData.SESSION_REPEAT_KEY+" int NOT NULL DEFAULT 1");
            break ;
          }
        }
      }
    }
}